package com.cyt.sys.util;

import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.Map;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelTest {
    public static void main(String[] args) {
        String file = "excel021.xlsx";
        try {
            excelChange(file);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    public static void excelChange(String path) throws IOException {
        File file = new File(path);

        // 判断文件是否存在
        if (!file.exists()) {
            // 创建文件
            file.createNewFile();
        }

        Workbook workBook = null;

        // 根据文件名称后缀，创建excel模板
        if (file.getName().endsWith(".xls")) {
            workBook = new HSSFWorkbook();// .xls后缀模板
        } else if (file.getName().endsWith(".xlsx")) {
            workBook = new XSSFWorkbook();// .xlsx后缀模板
        }

        // 创建工作薄
        Sheet sheet = workBook.createSheet("sheet2");

//		设置表头
        Row row = sheet.createRow(0);
        row.createCell(0).setCellValue("省份");
        row.createCell(1).setCellValue("城市");
        row.createCell(2).setCellValue("县城");

//		省份
        String[] provinces = {"福建省","广东省"};

        setBulids(provinces,workBook);

        setCityChangeVals(provinces,workBook,sheet);


//		把excel写入到文件中
        OutputStream out = FileUtils.openOutputStream(file);
        workBook.write(out);
        out.close();
    }

    public static void setBulids(String[] provinces,Workbook workBook) {
        Sheet sheet = workBook.createSheet("联动列表");
        for (int i = 0; i < provinces.length; i++) {
            Row row = sheet.createRow(i);
            row.createCell(0).setCellValue(provinces[i]);
        }
    }
    /**
     * 设置联动信息
     * @param sheet
     */
    public static void setCityChangeVals(String[] provinces,Workbook workBook,Sheet sheet) {

        Sheet provincesroom = workBook.createSheet("场地列表");
        for (int i = 0; i < provinces.length; i++) {
            Row row = provincesroom.createRow(i);
            row.createCell(0).setCellValue(provinces[i]);
        }

        DataValidationHelper helper = sheet.getDataValidationHelper();
        // 设置下拉框选项的值
//			设置下拉框位置
        CellRangeAddressList provRangeAddressList = new CellRangeAddressList(2, 100, 4, 4);
        DataValidationConstraint provincesconstraint = helper.createFormulaListConstraint("场地列表!$A$1:$A$10000");
        DataValidation provincesdataValidation= helper.createValidation(provincesconstraint, provRangeAddressList);
        sheet.addValidationData(provincesdataValidation);
    }


    /**
     * 设置联动信息
     * @param sheet
     */
    public static void setDevicesTypeVals(String[] provinces,String[] contracts,String[] listServicer,String[]wbRooms,Workbook workBook,Sheet sheet) throws IOException {

        Sheet provincesroom = workBook.createSheet("设备分类");
        for (int i = 0; i < provinces.length; i++) {
            Row row = provincesroom.createRow(i);
            row.createCell(0).setCellValue(provinces[i]);
        }

        Sheet contract = workBook.createSheet("合同列表");
        for (int i = 0; i < contracts.length; i++) {
            Row row = contract.createRow(i);
            row.createCell(0).setCellValue(contracts[i]);
        }

        Sheet servicer = workBook.createSheet("服务商列表");
        for (int i = 0; i < listServicer.length; i++) {
            Row row = servicer.createRow(i);
            row.createCell(0).setCellValue(listServicer[i]);
        }

        Sheet sheetroom = workBook.createSheet("房间列表");
        for (int i = 0; i < wbRooms.length; i++) {
            Row row = sheetroom.createRow(i);
            row.createCell(0).setCellValue(wbRooms[i]);
        }
        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidationConstraint provincesconstraint = helper.createFormulaListConstraint("设备分类!$A$1:$A$10000");
        CellRangeAddressList provincesregions = new CellRangeAddressList(2   ,100, 2, 2);
        DataValidationConstraint contractconstraint = helper.createFormulaListConstraint("合同列表!$A$1:$A$10000");
        CellRangeAddressList contractsregions = new CellRangeAddressList(2   ,100, 11, 11);
        DataValidationConstraint servicerconstraint = helper.createFormulaListConstraint("服务商列表!$A$1:$A$10000");
        CellRangeAddressList servicersregions = new CellRangeAddressList(2   ,100, 10, 10);
        DataValidationConstraint sheetroomconstraint = helper.createFormulaListConstraint("房间列表!$A$1:$A$10000");
        CellRangeAddressList sheetroomregions = new CellRangeAddressList(2   ,100, 5, 5);
        DataValidation provincesdataValidation= helper.createValidation(provincesconstraint, provincesregions);
        DataValidation contractdataValidation= helper.createValidation(contractconstraint, contractsregions);
        DataValidation servicerdataValidation= helper.createValidation(servicerconstraint, servicersregions);
        DataValidation sheetroomdataValidation= helper.createValidation(sheetroomconstraint, sheetroomregions);
        sheet.addValidationData(provincesdataValidation);
        sheet.addValidationData(contractdataValidation);
        sheet.addValidationData(servicerdataValidation);
        sheet.addValidationData(sheetroomdataValidation);
    }

    /**
     * 设置有效性
     * @param start 主影响单元格所在列，即此单元格由哪个单元格影响联动
     * @param sheet
     * @param rowNum 行数
     * @param colNum 列数
     */
    private static void setDataValidation(String file, String start, Sheet sheet, int rowNum, int colNum) {
        // TODO Auto-generated method stub
        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidation data_validation_list;
        data_validation_list = getDataValidationByFormula(file, "INDIRECT($" + (start + rowNum) + ")", rowNum, colNum,helper);
        sheet.addValidationData(data_validation_list);
    }

    private static DataValidation getDataValidationByFormula(String file, String string, int rowNum, int colNum,
                                                             DataValidationHelper helper) {
        // 加载下拉列表内容
        // 举例：若string = "INDIRECT($A$2)" 表示规则数据会从名称管理器中获取key与单元格 A2 值相同的数据，
        DataValidationConstraint constraint = helper.createFormulaListConstraint(string);

        int firstRow = rowNum -1;
        int lastRow = rowNum - 1;
        int firstCol = colNum - 1;
        int lastCol = colNum - 1;
        CellRangeAddressList regions = new CellRangeAddressList(firstRow,lastRow, firstCol, lastCol);

        DataValidation data_validation_list = helper.createValidation(constraint, regions);
//        data_validation_list.setEmptyCellAllowed(false);

        if (file.endsWith(".xlsx")) {
//			设置开启错误提示
            data_validation_list.setShowErrorBox(true);
//			设置提示信息
            data_validation_list.setShowPromptBox(true);
        }

        // 设置输入信息提示信息
        data_validation_list.createPromptBox("下拉选择提示", "请使用下拉方式选择合适的值！");
        // 设置输入错误提示信息
        //data_validation_list.createErrorBox("选择错误提示", "你输入的值未在备选列表中，请下拉选择合适的值！");
        return data_validation_list;
    }
    /**
     *
     * @param start 偏移量，如果给0，表示从A列开始，1，就是从B列
     * @param rowNum 第几行
     * @param colCount 一共多少列
     * @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1
     */
    private static String getRange(int start, int rowNum, int colCount) {
//		开始列
        char rowStart = (char) ('A' + start);
        char end = (char) (rowStart + colCount - 1);
        return "$" + rowStart + "$" + rowNum + ":$" + end + "$" + rowNum;
    }
}

